<html>
<head>
	<title>Using the SQL Relay drop-in replacement library for PostgreSQL</title>
	<link rel="stylesheet" href="../css/styles.css">
</head>
<body>
<h1>Using the SQL Relay drop-in replacement library for PostgreSQL</h1>

<ul>
  <li><a href="#whatis">What is a drop-in replacement library?</a></li>
  <li><a href="#commandline">Using the drop-in replacement library with command-line programs</a></li>
  <li><a href="#daemons">Using the drop-in replacement library with daemons</a></li>
  <li><a href="#inetd">Using the drop-in replacement library with inetd/xinetd helper programs</a></li>
  <li><a href="#modules">Using the drop-in replacement library with modules</a></li>
  <li><a href="#work">Function support</a></li>
</ul>

<a name="whatis"/><h2>What is a drop-in replacement library?</h2>

<p>The SQL Relay Drop-In Replacement Library for PostgreSQL is a shared-object library that can be LD_PRELOAD'ed to take the place of the native PostgreSQL client library.</p>

<p>It allows many applications that are written to use PostgreSQL directly to use SQL Relay without modification.  This allows an app to immediately take advantage of SQL Relay features such as connection pooling, load balancing, query routing and throttling.  It can also be used to aim an app that was written to use PostgreSQL at a different database.</p>

<a name="commandline"/><h2>Using the drop-in replacement library with command-line programs</h2>

<p>You can use the SQL Relay drop-in replacement library for PostgreSQL clients
by loading the drop-in library and running your program.</p>

<p>The parameters that would ordinarily indicate which host, port, socket,
username and password to use to connect to PostgreSQL will be used as
parameters to connect to SQL Relay.  The parameter that would ordinarily
indicate which database to connect to will be ignored.  Instances of SQL Relay
are configured to connect to a single database, and that database will be used
by the client program.</p>

<p>In the following example, we're running the "psql" program against an
instance of SQL Relay running on the localhost, port 9000 against an Oracle
database.  This instance of SQL Relay is configured with a username/password of
oracleuser/oraclepass.</p>

<p>For sh-based shells:</p>

<blockquote>
  <pre>LD_PRELOAD=/usr/local/firstworks/lib/libpqsqlrelay.so.4
export LD_PRELOAD
psql -h localhost -p 9000 -U oracleuser -W
Password: oraclepass
</pre>

</blockquote>
<p>For csh-based shells:</p>

<blockquote>
  <pre>setenv LD_PRELOAD /usr/local/firstworks/lib/libpqsqlrelay.so.4
psql -h localhost -p 9000 -U oracleuser -W
Password: oraclepass
</pre>

</blockquote>
<p>The LD_PRELOAD environment variable instructs the dynamic loader to load
libpqsqlrelay.so before loading any other libraries for any programs.
The psql client program will still load the native PostgreSQL client library,
but since it loaded the SQL Relay drop-in replacement library first, function
calls that would normally be fulfilled by the native PostgreSQL client library
are fulfilled by the SQL Relay drop-in replacement library instead.</p>

<p>Below is a sample session using the psql command line client against an
Oracle database through SQL Relay.</p>

<blockquote>
  <pre>Welcome to psql 7.3.4-RH, the !PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

=> create table testtable (col1 varchar2(60), col2 number(5,2));

=> insert into testtable values ('hello',123.45);

=> select * from testtable;
 COL1  |  COL2
-------+--------
 hello | 123.45
(1 row)

=> \q

</pre>

</blockquote>
<a name="daemons"/><h2>Using the drop-in replacement library with daemons</h2>

<p>Using the SQL Relay drop-in replacement library for PostgreSQL with daemons
is simlar to using it on the command line.  You just need to add the LD_PRELOAD
command to the startup script for the daemon before the command that starts the
daemon itself.</p>

<p>This goes for using the drop-in replacement library with PHP too.  You just
need to add the LD_PRELOAD command to the startup script for the http
daemon.</p>

<p>If your system uses systemd instead of init scripts, then you can modify the systemd configuration to make the LD_PRELOAD setting part of the environment.  For example, the Apache webserver is typically started by the systemd config file /lib/systemd/system/httpd.service.  To configure Apache, create a new file /etc/systemd/system/httpd.service with the following contents:</p>

<blockquote>
  <pre>.include /lib/systemd/system/httpd.service
[Service]
Environment=LD_PRELOAD=/usr/local/firstworks/lib/libmysql51sqlrelay.so.4
</pre>

</blockquote>
<a name="inetd"/><h2>Using the drop-in replacement library with inetd/xinetd helper programs</h2>

<p>Inetd and xinetd are daemons that listen on ports and run helper programs to
service requests on those ports.  The helper programs die off after the request
is serviced.</p>

<p>The easist way to get an inetd helper program to use the SQL Relay drop-in
replacement library for PostgreSQL is to add the LD_PRELOAD command to the
startup script for inetd/xinetd.  Any command that inetd/xinetd runs will also
preload the library.</p>

<p>However, if some of the helper programs need to actually run against
PostgreSQL and not against SQL Relay, then you will have to do something
different.  The easiest thing to do is create a script for each helper program
that needs to run against SQL Relay that runs the LD_PRELOAD command and then
runs the actual helper program, passing it all the necessary command line
arguments.</p>

<p>For example, lets say you have a pop3 server called pop3d that uses
PostgreSQL for user identification and you wanted to use SQL Relay instead of
PostgreSQL.  The inetd.conf entry might look like this:</p>

<blockquote>
  <pre>pop3 stream tcp nowait root /usr/local/bin/pop3d
</pre>

</blockquote>
<p>An /etc/xinetd.d entry might look like this:</p>

<blockquote>
  <pre>service pop3
{
	socket_type	= stream
	wait		= no
	user		= root
	server		= /usr/local/bin/pop3d
}
</pre>

</blockquote>
<p>You could write the a script called /usr/local/bin/pop3d-sqlrelay as
follows:</p>

<blockquote>
  <pre>#!/bin/sh
LD_PRELOAD=/usr/local/firstworks/lib/libpqsqlrelay.so.4
export LD_PRELOAD
/usr/local/bin/pop3d $@
</pre>

</blockquote>
<p>And modify the entries to call the script instead of pop3d as follows:</p>

<blockquote>
  <pre>pop3 stream tcp nowait root /usr/local/bin/pop3d-sqlrelay
</pre>

</blockquote>
<p>Or for xinetd:</p>

<blockquote>
  <pre>service pop3
{
	socket_type	= stream
	wait		= no
	user		= root
	server		= /usr/local/bin/pop3d-sqlrelay
}
</pre>

</blockquote>
<a name="modules"/><h2>Using the drop-in replacement library with modules</h2>

<p>You may want to use the SQL Relay drop-in replacement library for PostgreSQL
clients with a program that isn't compiled against the native PostgreSQL client
library but rather loads it as a module such as a program that uses ODBC or
Perl DBI, or an Apache/PHP application.</p>

<p>Using the SQL Relay drop-in replacement library with programs that load
the native PostgreSQL client library as a module is simlar to using it on the
command line.  You just need to make sure that the LD_PRELOAD command is run
before the program starts.</p>

<p>If the program is a command line program, then run the LD_PRELOAD command
before running your program.  Even though the program ultimately loads the
native PostgreSQL client library, all of its functions will be overriden by the
SQL Relay drop-in replacement library.</p>

<p>If the program is a daemon then add the LD_PRELOAD command to the startup
script or systemd configuration for the daemon.</p>

<p>If the program runs in the address space of a daemon, such as a PHP
application running under Apache's mod_php, then add the LD_PRELOAD command to
the startup script or systemd configuration for the daemon.  The caveat here is
that all applications running in the address space of the daemon will use the
drop-in replacement library instead of the native PostgreSQL library.  It is
not possible, for example for a web server to run one PHP application directly
against PostgreSQL and another PHP application against SQL Relay using the
drop-in replacement library; if the drop-in replacement library is loaded, both
applications will end up using it.</p>

<p>If the program is spawned by a daemon, such as a cgi spawned by a web-server
or an inetd/xinetd helper program, then you can either add the LD_PRELOAD
command to the daemon's startup script/systemd configuration or write a script
to run the LD_PRELOAD command and pass along the command line arguments (see
the section
<a href="#inetd">Using the drop-in replacement library with inetd/xinetd helper
programs above</a>).</p>

<a name="work"/><h2>Function support</h2>

<p>The SQL Relay drop-in replacement library for PostgreSQL implements most of
the native PostgreSQL client library's functions, but there are a few functions
that aren't implemented because SQL Relay doesn't have a good way to support
them.  These functions return safe values or a failure condition.</p>

<p>Here is a list of functions that are implemented and functions that are not.
If your application uses one of the functions that is not implemented, you may
or may not be able to use it with the SQL Relay drop-in replacement library for
PostgreSQL.</p>

<ul>
  <li><b>PQsetdbLogin</b> - yes, but ignores the options and tty parameters</li>
  <li><b>PQsetdb</b> - yes, but ignores the options and tty parameters</li>
  <li><b>PQconnectdb</b> - yes, but ignores the options and tty parameters</li>
  <li><b>PQconnectdbParams</b> - yes, but ignores the options and tty parameters</li>
  <li><b>PQfinish</b> - yes</li>
  <li><b>PQreset</b> - yes</li>
  <li><b>PQdb</b> - yes</li>
  <li><b>PQuser</b> - yes</li>
  <li><b>PQpass</b> - yes</li>
  <li><b>PQhost</b> - yes</li>
  <li><b>PQport</b> - yes</li>
  <li><b>PQtty</b> - yes</li>
  <li><b>PQoptions</b> - yes</li>
  <li><b>PQstatus</b> - yes</li>
  <li><b>PQsetErrorVerbosity</b> - has no effect</li>
  <li><b>PQerrorMessage</b> - yes</li>
  <li><b>PQsocket</b> - always returns -1</li>
  <li><b>PQbackendPID</b> - always returns -1</li>
  <li><b>PQgetssl</b> - always returns 0</li>
  <li><b>PQclientEncoding</b> - yes</li>
  <li><b>PQsetClientEncoding</b> - has no effect</li>
  <li><b>PQsetNoticeProcessor</b> - yes</li>
  <li><b>PQparameterStatus</b> - returns dummy values</li>
  <li><b>PQserverVersion</b> - returns dummy value of 80100</li>
  <li><b>PQprotocolVersion</b> - returns dummy value of 3</li>
  <li><b>PQescapeString</b> - yes</li>
  <li><b>PQescapeBytea</b> - yes</li>
  <li><b>PQescapeByteConn</b> - yes, but just calls PQescapeBytea</li>
  <li><b>PQunescapeBytea</b> - yes</li>
  <li><b>PQclear</b> - yes</li>
  <li><b>PQprepare</b> - yes</li>
  <li><b>PQexecPrepared</b> - yes</li>
  <li><b>PQexecParams</b> - yes</li>
  <li><b>PQexec</b> - yes</li>
  <li><b>PQdescribePrepared</b> - always returns NULL</li>
  <li><b>PQdescribePortal</b> - always returns NULL</li>
  <li><b>PQresultStatus</b> - yes</li>
  <li><b>PQresStatus</b> - yes</li>
  <li><b>PQresultErrorMessage</b> - yes, returns the SQL Relay error string</li>
  <li><b>PQresultErrorField</b> - yes, but just calls PQresultErrorMessage</li>
  <li><b>PQntuples</b> - yes</li>
  <li><b>PQnfields</b> - yes</li>
  <li><b>PQbinaryTuples</b> - yes</li>
  <li><b>PQfname</b> - yes</li>
  <li><b>PQfnumber</b> - yes</li>
  <li><b>PQftable</b> - always returns InvalidOid</li>
  <li><b>PQftablecol</b> - always returns InvalidOid</li>
  <li><b>PQfformat</b> - yes</li>
  <li><b>PQftype</b> - yes</li>
  <li><b>PQfsize</b> - yes</li>
  <li><b>PQfmod</b> - yes</li>
  <li><b>PQcmdStatus</b> - only returns "" or "SELECT"</li>
  <li><b>PQoidStatus</b> - always returns InvalidOid</li>
  <li><b>PQoidValue</b> - always returns InvalidOid</li>
  <li><b>PQcmdTuples</b> - yes</li>
  <li><b>PQgetvalue</b> - yes</li>
  <li><b>PQgetlength</b> - yes</li>
  <li><b>PQgetisnull</b> - yes</li>
  <li><b>PQmakeEmptyPGresult</b> - yes</li>
  <li><b>PQnparams</b> - always returns 0</li>
  <li><b>PQnparamtype</b> - always returns 0</li>
  <li><b>PQprint</b> - yes</li>
  <li><b>PQdisplayTuples</b> - yes</li>
  <li><b>PQprintTuples</b> - yes</li>
  <li><b>PQconninfoParse</b> - always returns NULL</li>
  <li><b>PQconndefaults</b> - always returns NULL</li>
  <li><b>PQconninfoFree</b> - yes</li>
  <li><b>PQtrace</b> - just calls sqlrconnection::debugOn()</li>
  <li><b>PQuntrace</b> - just calls sqlrconnection::debugOff()</li>
  <li><b>PQgetline</b> - NULL terminates string and returns EOF</li>
  <li><b>PQputline</b> - always returns EOF</li>
  <li><b>PQgetlineAsync</b> - NULL terminates buffer and returns EOF</li>
  <li><b>PQputnbytes</b> - always returns EOF</li>
  <li><b>PQendcopy</b> - always returns -1 for failure</li>
  <li><b>PQmblen</b> - always returns 1</li>
  <li><b>PQenv2encoding</b> - yes</li>
  <li><b>PQfn</b> - does nothing, always returns NULL</li>
  <li><b>PQfreemem</b> - yes</li>
  <li><b>lo_open</b> - does nothing, always returns -1</li>
  <li><b>lo_close</b> - does nothing, always returns -1</li>
  <li><b>lo_read</b> - does nothing, always returns -1</li>
  <li><b>lo_write</b> - does nothing, always returns -1</li>
  <li><b>lo_lseek</b> - does nothing, always returns -1</li>
  <li><b>lo_creat</b> - does nothing, always returns -1</li>
  <li><b>lo_tell</b> - does nothing, always returns -1</li>
  <li><b>lo_unlink</b> - does nothing, always returns -1</li>
  <li><b>lo_import</b> - does nothing, always returns -1</li>
  <li><b>lo_export</b> - does nothing, always returns -1</li>
  <li><b>PQnotifies</b> - does nothing, always returns NULL</li>
  <li><b>PQfreeNotify</b> - does nothing</li>
</ul>

<p>The following functions implement the PostgreSQL asynchronous query API.
SQL Relay doesn't have an asynchronous query API so they are implemented
as calls to synchronous query functions.  They work, but in a synchronous
manner.</p>

<ul>
  <li><b>PQconnectStart</b></li>
  <li><b>PQconnectPoll</b></li>
  <li><b>PQresetStart</b></li>
  <li><b>PQresetPoll</b></li>
  <li><b>PQgetCancel</b></li>
  <li><b>PQfreeCancel</b></li>
  <li><b>PQcancel</b></li>
  <li><b>PQrequestCancel</b></li>
  <li><b>PQsendQuery</b></li>
  <li><b>PQgetResult</b></li>
  <li><b>PQtransactionStatus</b></li>
  <li><b>PQisBusy</b></li>
  <li><b>PQconsumeInput</b></li>
  <li><b>PQsetnonblocking</b></li>
  <li><b>PQisnonblocking</b></li>
  <li><b>PQflush</b></li>
  <li><b>PQsendSome</b>
</li>
</ul>

</body>
</html>
